In [1]:
import pymysql
db = pymysql.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"sakila",
charset='utf8',
)
In [2]:
film_df = pd.read_sql("SELECT * FROM film;", db)
In [3]:
film_df.head(1)
Out[3]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
In [5]:
SQL_QUERY = """
SELECT *
FROM film
WHERE
(release_year = 2006 OR release_year = 2007)
AND (rating = "PG" OR rating = "G")
;
"""
pd.read_sql(SQL_QUERY, db)
Out[5]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
1
2
ACE GOLDFINGER
A Astounding Epistle of a Database Administrat...
2006
1
None
3
4.99
48
12.99
G
Trailers,Deleted Scenes
2006-02-15 05:03:42
2
4
AFFAIR PREJUDICE
A Fanciful Documentary of a Frisbee And a Lumb...
2006
1
None
5
2.99
117
26.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
3
5
AFRICAN EGG
A Fast-Paced Documentary of a Pastry Chef And ...
2006
1
None
6
2.99
130
22.99
G
Deleted Scenes
2006-02-15 05:03:42
4
6
AGENT TRUMAN
A Intrepid Panorama of a Robot And a Boy who m...
2006
1
None
3
2.99
169
17.99
PG
Deleted Scenes
2006-02-15 05:03:42
5
11
ALAMO VIDEOTAPE
A Boring Epistle of a Butler And a Cat who mus...
2006
1
None
6
0.99
126
16.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
6
12
ALASKA PHANTOM
A Fanciful Saga of a Hunter And a Pastry Chef ...
2006
1
None
6
0.99
136
22.99
PG
Commentaries,Deleted Scenes
2006-02-15 05:03:42
7
13
ALI FOREVER
A Action-Packed Drama of a Dentist And a Croco...
2006
1
None
4
4.99
150
21.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
8
19
AMADEUS HOLY
A Emotional Display of a Pioneer And a Technic...
2006
1
None
6
0.99
113
20.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
9
22
AMISTAD MIDSUMMER
A Emotional Character Study of a Dentist And a...
2006
1
None
6
2.99
85
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
10
25
ANGELS LIFE
A Thoughtful Display of a Woman And a Astronau...
2006
1
None
3
2.99
74
15.99
G
Trailers
2006-02-15 05:03:42
11
26
ANNIE IDENTITY
A Amazing Panorama of a Pastry Chef And a Boat...
2006
1
None
3
0.99
86
15.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
12
37
ARIZONA BANG
A Brilliant Panorama of a Mad Scientist And a ...
2006
1
None
3
2.99
121
28.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
13
39
ARMAGEDDON LOST
A Fast-Paced Tale of a Boat And a Teacher who ...
2006
1
None
5
0.99
99
10.99
G
Trailers
2006-02-15 05:03:42
14
41
ARSENIC INDEPENDENCE
A Fanciful Documentary of a Mad Cow And a Woma...
2006
1
None
4
0.99
137
17.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
15
43
ATLANTIS CAUSE
A Thrilling Yarn of a Feminist And a Hunter wh...
2006
1
None
6
2.99
170
15.99
G
Behind the Scenes
2006-02-15 05:03:42
16
46
AUTUMN CROW
A Beautiful Tale of a Dentist And a Mad Cow wh...
2006
1
None
3
4.99
108
13.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
17
50
BAKED CLEOPATRA
A Stunning Drama of a Forensic Psychologist An...
2006
1
None
3
2.99
182
20.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
18
52
BALLROOM MOCKINGBIRD
A Thrilling Documentary of a Composer And a Mo...
2006
1
None
6
0.99
173
29.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
19
55
BARBARELLA STREETCAR
A Awe-Inspiring Story of a Feminist And a Cat ...
2006
1
None
6
2.99
65
27.99
G
Behind the Scenes
2006-02-15 05:03:42
20
56
BAREFOOT MANCHURIAN
A Intrepid Story of a Cat And a Student who mu...
2006
1
None
6
2.99
129
15.99
G
Trailers,Commentaries
2006-02-15 05:03:42
21
58
BEACH HEARTBREAKERS
A Fateful Display of a Womanizer And a Mad Sci...
2006
1
None
6
2.99
122
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
22
61
BEAUTY GREASE
A Fast-Paced Display of a Composer And a Moose...
2006
1
None
5
4.99
175
28.99
G
Trailers,Commentaries
2006-02-15 05:03:42
23
63
BEDAZZLED MARRIED
A Astounding Character Study of a Madman And a...
2006
1
None
6
0.99
73
21.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
24
65
BEHAVIOR RUNAWAY
A Unbelieveable Drama of a Student And a Husba...
2006
1
None
3
4.99
100
20.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
25
72
BILL OTHERS
A Stunning Saga of a Mad Scientist And a Foren...
2006
1
None
6
2.99
93
12.99
PG
Trailers,Commentaries
2006-02-15 05:03:42
26
74
BIRCH ANTITRUST
A Fanciful Panorama of a Husband And a Pioneer...
2006
1
None
4
4.99
162
18.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
27
75
BIRD INDEPENDENCE
A Thrilling Documentary of a Car And a Student...
2006
1
None
6
4.99
163
14.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
28
77
BIRDS PERDITION
A Boring Story of a Womanizer And a Pioneer wh...
2006
1
None
5
4.99
61
15.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
29
78
BLACKOUT PRIVATE
A Intrepid Yarn of a Pastry Chef And a Mad Sci...
2006
1
None
7
2.99
85
12.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
...
...
...
...
...
...
...
...
...
...
...
...
...
...
342
914
TROUBLE DATE
A Lacklusture Panorama of a Forensic Psycholog...
2006
1
None
6
2.99
61
13.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
343
915
TRUMAN CRAZY
A Thrilling Epistle of a Moose And a Boy who m...
2006
1
None
7
4.99
92
9.99
G
Trailers,Commentaries
2006-02-15 05:03:42
344
916
TURN STAR
A Stunning Tale of a Man And a Monkey who must...
2006
1
None
3
2.99
80
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
345
918
TWISTED PIRATES
A Touching Display of a Frisbee And a Boat who...
2006
1
None
4
4.99
152
23.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
346
919
TYCOON GATHERING
A Emotional Display of a Husband And a A Shark...
2006
1
None
3
4.99
82
17.99
G
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
347
920
UNBREAKABLE KARATE
A Amazing Character Study of a Robot And a Stu...
2006
1
None
3
0.99
62
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
348
924
UNFORGIVEN ZOOLANDER
A Taut Epistle of a Monkey And a Sumo Wrestler...
2006
1
None
7
0.99
129
15.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
349
928
UPTOWN YOUNG
A Fateful Documentary of a Dog And a Hunter wh...
2006
1
None
5
2.99
84
16.99
PG
Commentaries
2006-02-15 05:03:42
350
932
VALLEY PACKER
A Astounding Documentary of a Astronaut And a ...
2006
1
None
3
0.99
73
21.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
351
950
VOLUME HOUSE
A Boring Tale of a Dog And a Woman who must Me...
2006
1
None
7
4.99
132
12.99
PG
Commentaries
2006-02-15 05:03:42
352
952
WAGON JAWS
A Intrepid Drama of a Moose And a Boat who mus...
2006
1
None
7
2.99
152
17.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
353
954
WAKE JAWS
A Beautiful Saga of a Feminist And a Composer ...
2006
1
None
7
4.99
73
18.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
354
955
WALLS ARTIST
A Insightful Panorama of a Teacher And a Teach...
2006
1
None
7
4.99
135
19.99
PG
Trailers,Behind the Scenes
2006-02-15 05:03:42
355
957
WAR NOTTING
A Boring Drama of a Teacher And a Sumo Wrestle...
2006
1
None
7
4.99
80
26.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
356
958
WARDROBE PHANTOM
A Action-Packed Display of a Mad Cow And a Ast...
2006
1
None
6
2.99
178
19.99
G
Trailers,Commentaries
2006-02-15 05:03:42
357
959
WARLOCK WEREWOLF
A Astounding Yarn of a Pioneer And a Crocodile...
2006
1
None
6
2.99
83
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
358
960
WARS PLUTO
A Taut Reflection of a Teacher And a Database ...
2006
1
None
5
2.99
128
15.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
359
962
WASTELAND DIVINE
A Fanciful Story of a Database Administrator A...
2006
1
None
7
2.99
85
18.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
360
963
WATCH TRACY
A Fast-Paced Yarn of a Dog And a Frisbee who m...
2006
1
None
5
0.99
78
12.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
361
964
WATERFRONT DELIVERANCE
A Unbelieveable Documentary of a Dentist And a...
2006
1
None
4
4.99
61
17.99
G
Behind the Scenes
2006-02-15 05:03:42
362
965
WATERSHIP FRONTIER
A Emotional Yarn of a Boat And a Crocodile who...
2006
1
None
6
0.99
112
28.99
G
Commentaries
2006-02-15 05:03:42
363
966
WEDDING APOLLO
A Action-Packed Tale of a Student And a Waitre...
2006
1
None
3
0.99
70
14.99
PG
Trailers
2006-02-15 05:03:42
364
968
WEREWOLF LOLA
A Fanciful Story of a Man And a Sumo Wrestler ...
2006
1
None
6
4.99
79
19.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
365
969
WEST LION
A Intrepid Drama of a Butler And a Lumberjack ...
2006
1
None
4
4.99
159
29.99
G
Trailers
2006-02-15 05:03:42
366
980
WIZARD COLDBLOODED
A Lacklusture Display of a Robot And a Girl wh...
2006
1
None
4
4.99
75
12.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
367
983
WON DARES
A Unbelieveable Documentary of a Teacher And a...
2006
1
None
7
2.99
105
18.99
PG
Behind the Scenes
2006-02-15 05:03:42
368
985
WONDERLAND CHRISTMAS
A Awe-Inspiring Character Study of a Waitress ...
2006
1
None
4
4.99
111
19.99
PG
Commentaries
2006-02-15 05:03:42
369
987
WORDS HUNTER
A Action-Packed Reflection of a Composer And a...
2006
1
None
3
2.99
116
13.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
370
991
WORST BANGER
A Thrilling Drama of a Madman And a Dentist wh...
2006
1
None
4
2.99
185
26.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
371
996
YOUNG LANGUAGE
A Unbelieveable Yarn of a Boat And a Database ...
2006
1
None
6
0.99
183
9.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
372 rows × 13 columns
In [7]:
SQL_QUERY = """
SELECT COUNT(*)
FROM film
WHERE
release_year IN (2006, 2007)
AND rating IN ("PG", "G")
;
"""
pd.read_sql(SQL_QUERY, db)
Out[7]:
COUNT(*)
0
372
In [10]:
is_pg_or_g = film_df.rating.isin(["PG", "G"])
is_2006_or_2007 = film_df.release_year.isin([2006, 2007])
In [11]:
film_df[is_pg_or_g & is_2006_or_2007]
Out[11]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
1
2
ACE GOLDFINGER
A Astounding Epistle of a Database Administrat...
2006
1
None
3
4.99
48
12.99
G
Trailers,Deleted Scenes
2006-02-15 05:03:42
3
4
AFFAIR PREJUDICE
A Fanciful Documentary of a Frisbee And a Lumb...
2006
1
None
5
2.99
117
26.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
4
5
AFRICAN EGG
A Fast-Paced Documentary of a Pastry Chef And ...
2006
1
None
6
2.99
130
22.99
G
Deleted Scenes
2006-02-15 05:03:42
5
6
AGENT TRUMAN
A Intrepid Panorama of a Robot And a Boy who m...
2006
1
None
3
2.99
169
17.99
PG
Deleted Scenes
2006-02-15 05:03:42
10
11
ALAMO VIDEOTAPE
A Boring Epistle of a Butler And a Cat who mus...
2006
1
None
6
0.99
126
16.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
11
12
ALASKA PHANTOM
A Fanciful Saga of a Hunter And a Pastry Chef ...
2006
1
None
6
0.99
136
22.99
PG
Commentaries,Deleted Scenes
2006-02-15 05:03:42
12
13
ALI FOREVER
A Action-Packed Drama of a Dentist And a Croco...
2006
1
None
4
4.99
150
21.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
18
19
AMADEUS HOLY
A Emotional Display of a Pioneer And a Technic...
2006
1
None
6
0.99
113
20.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
21
22
AMISTAD MIDSUMMER
A Emotional Character Study of a Dentist And a...
2006
1
None
6
2.99
85
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
24
25
ANGELS LIFE
A Thoughtful Display of a Woman And a Astronau...
2006
1
None
3
2.99
74
15.99
G
Trailers
2006-02-15 05:03:42
25
26
ANNIE IDENTITY
A Amazing Panorama of a Pastry Chef And a Boat...
2006
1
None
3
0.99
86
15.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
36
37
ARIZONA BANG
A Brilliant Panorama of a Mad Scientist And a ...
2006
1
None
3
2.99
121
28.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
38
39
ARMAGEDDON LOST
A Fast-Paced Tale of a Boat And a Teacher who ...
2006
1
None
5
0.99
99
10.99
G
Trailers
2006-02-15 05:03:42
40
41
ARSENIC INDEPENDENCE
A Fanciful Documentary of a Mad Cow And a Woma...
2006
1
None
4
0.99
137
17.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
42
43
ATLANTIS CAUSE
A Thrilling Yarn of a Feminist And a Hunter wh...
2006
1
None
6
2.99
170
15.99
G
Behind the Scenes
2006-02-15 05:03:42
45
46
AUTUMN CROW
A Beautiful Tale of a Dentist And a Mad Cow wh...
2006
1
None
3
4.99
108
13.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
49
50
BAKED CLEOPATRA
A Stunning Drama of a Forensic Psychologist An...
2006
1
None
3
2.99
182
20.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
51
52
BALLROOM MOCKINGBIRD
A Thrilling Documentary of a Composer And a Mo...
2006
1
None
6
0.99
173
29.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
54
55
BARBARELLA STREETCAR
A Awe-Inspiring Story of a Feminist And a Cat ...
2006
1
None
6
2.99
65
27.99
G
Behind the Scenes
2006-02-15 05:03:42
55
56
BAREFOOT MANCHURIAN
A Intrepid Story of a Cat And a Student who mu...
2006
1
None
6
2.99
129
15.99
G
Trailers,Commentaries
2006-02-15 05:03:42
57
58
BEACH HEARTBREAKERS
A Fateful Display of a Womanizer And a Mad Sci...
2006
1
None
6
2.99
122
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
60
61
BEAUTY GREASE
A Fast-Paced Display of a Composer And a Moose...
2006
1
None
5
4.99
175
28.99
G
Trailers,Commentaries
2006-02-15 05:03:42
62
63
BEDAZZLED MARRIED
A Astounding Character Study of a Madman And a...
2006
1
None
6
0.99
73
21.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
64
65
BEHAVIOR RUNAWAY
A Unbelieveable Drama of a Student And a Husba...
2006
1
None
3
4.99
100
20.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
71
72
BILL OTHERS
A Stunning Saga of a Mad Scientist And a Foren...
2006
1
None
6
2.99
93
12.99
PG
Trailers,Commentaries
2006-02-15 05:03:42
73
74
BIRCH ANTITRUST
A Fanciful Panorama of a Husband And a Pioneer...
2006
1
None
4
4.99
162
18.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
74
75
BIRD INDEPENDENCE
A Thrilling Documentary of a Car And a Student...
2006
1
None
6
4.99
163
14.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
76
77
BIRDS PERDITION
A Boring Story of a Womanizer And a Pioneer wh...
2006
1
None
5
4.99
61
15.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
77
78
BLACKOUT PRIVATE
A Intrepid Yarn of a Pastry Chef And a Mad Sci...
2006
1
None
7
2.99
85
12.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
...
...
...
...
...
...
...
...
...
...
...
...
...
...
913
914
TROUBLE DATE
A Lacklusture Panorama of a Forensic Psycholog...
2006
1
None
6
2.99
61
13.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
914
915
TRUMAN CRAZY
A Thrilling Epistle of a Moose And a Boy who m...
2006
1
None
7
4.99
92
9.99
G
Trailers,Commentaries
2006-02-15 05:03:42
915
916
TURN STAR
A Stunning Tale of a Man And a Monkey who must...
2006
1
None
3
2.99
80
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
917
918
TWISTED PIRATES
A Touching Display of a Frisbee And a Boat who...
2006
1
None
4
4.99
152
23.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
918
919
TYCOON GATHERING
A Emotional Display of a Husband And a A Shark...
2006
1
None
3
4.99
82
17.99
G
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
919
920
UNBREAKABLE KARATE
A Amazing Character Study of a Robot And a Stu...
2006
1
None
3
0.99
62
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
923
924
UNFORGIVEN ZOOLANDER
A Taut Epistle of a Monkey And a Sumo Wrestler...
2006
1
None
7
0.99
129
15.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
927
928
UPTOWN YOUNG
A Fateful Documentary of a Dog And a Hunter wh...
2006
1
None
5
2.99
84
16.99
PG
Commentaries
2006-02-15 05:03:42
931
932
VALLEY PACKER
A Astounding Documentary of a Astronaut And a ...
2006
1
None
3
0.99
73
21.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
949
950
VOLUME HOUSE
A Boring Tale of a Dog And a Woman who must Me...
2006
1
None
7
4.99
132
12.99
PG
Commentaries
2006-02-15 05:03:42
951
952
WAGON JAWS
A Intrepid Drama of a Moose And a Boat who mus...
2006
1
None
7
2.99
152
17.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
953
954
WAKE JAWS
A Beautiful Saga of a Feminist And a Composer ...
2006
1
None
7
4.99
73
18.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
954
955
WALLS ARTIST
A Insightful Panorama of a Teacher And a Teach...
2006
1
None
7
4.99
135
19.99
PG
Trailers,Behind the Scenes
2006-02-15 05:03:42
956
957
WAR NOTTING
A Boring Drama of a Teacher And a Sumo Wrestle...
2006
1
None
7
4.99
80
26.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
957
958
WARDROBE PHANTOM
A Action-Packed Display of a Mad Cow And a Ast...
2006
1
None
6
2.99
178
19.99
G
Trailers,Commentaries
2006-02-15 05:03:42
958
959
WARLOCK WEREWOLF
A Astounding Yarn of a Pioneer And a Crocodile...
2006
1
None
6
2.99
83
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
959
960
WARS PLUTO
A Taut Reflection of a Teacher And a Database ...
2006
1
None
5
2.99
128
15.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
961
962
WASTELAND DIVINE
A Fanciful Story of a Database Administrator A...
2006
1
None
7
2.99
85
18.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
962
963
WATCH TRACY
A Fast-Paced Yarn of a Dog And a Frisbee who m...
2006
1
None
5
0.99
78
12.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
963
964
WATERFRONT DELIVERANCE
A Unbelieveable Documentary of a Dentist And a...
2006
1
None
4
4.99
61
17.99
G
Behind the Scenes
2006-02-15 05:03:42
964
965
WATERSHIP FRONTIER
A Emotional Yarn of a Boat And a Crocodile who...
2006
1
None
6
0.99
112
28.99
G
Commentaries
2006-02-15 05:03:42
965
966
WEDDING APOLLO
A Action-Packed Tale of a Student And a Waitre...
2006
1
None
3
0.99
70
14.99
PG
Trailers
2006-02-15 05:03:42
967
968
WEREWOLF LOLA
A Fanciful Story of a Man And a Sumo Wrestler ...
2006
1
None
6
4.99
79
19.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
968
969
WEST LION
A Intrepid Drama of a Butler And a Lumberjack ...
2006
1
None
4
4.99
159
29.99
G
Trailers
2006-02-15 05:03:42
979
980
WIZARD COLDBLOODED
A Lacklusture Display of a Robot And a Girl wh...
2006
1
None
4
4.99
75
12.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
982
983
WON DARES
A Unbelieveable Documentary of a Teacher And a...
2006
1
None
7
2.99
105
18.99
PG
Behind the Scenes
2006-02-15 05:03:42
984
985
WONDERLAND CHRISTMAS
A Awe-Inspiring Character Study of a Waitress ...
2006
1
None
4
4.99
111
19.99
PG
Commentaries
2006-02-15 05:03:42
986
987
WORDS HUNTER
A Action-Packed Reflection of a Composer And a...
2006
1
None
3
2.99
116
13.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
990
991
WORST BANGER
A Thrilling Drama of a Madman And a Dentist wh...
2006
1
None
4
2.99
185
26.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
995
996
YOUNG LANGUAGE
A Unbelieveable Yarn of a Boat And a Database ...
2006
1
None
6
0.99
183
9.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
372 rows × 13 columns
In [13]:
film_df[is_pg_or_g & is_2006_or_2007].count()
Out[13]:
film_id 372
title 372
description 372
release_year 372
language_id 372
original_language_id 0
rental_duration 372
rental_rate 372
length 372
replacement_cost 372
rating 372
special_features 372
last_update 372
dtype: int64
In [ ]:
In [14]:
film_df.head(1)
Out[14]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
In [16]:
SQL_QUERY = """
SELECT title, description, rental_rate
FROM film
WHERE
description LIKE "%Boring%"
AND rental_rate = 0.99
;
"""
pd.read_sql(SQL_QUERY, db)
Out[16]:
title
description
rental_rate
0
ALAMO VIDEOTAPE
A Boring Epistle of a Butler And a Cat who mus...
0.99
1
ARMY FLINTSTONES
A Boring Saga of a Database Administrator And ...
0.99
2
BLOOD ARGONAUTS
A Boring Drama of a Explorer And a Man who mus...
0.99
3
CATCH AMISTAD
A Boring Reflection of a Lumberjack And a Femi...
0.99
4
COMANCHEROS ENEMY
A Boring Saga of a Lumberjack And a Monkey who...
0.99
5
CURTAIN VIDEOTAPE
A Boring Reflection of a Dentist And a Mad Cow...
0.99
6
GRAPES FURY
A Boring Yarn of a Mad Cow And a Sumo Wrestler...
0.99
7
GUN BONNIE
A Boring Display of a Sumo Wrestler And a Husb...
0.99
8
JEOPARDY ENCINO
A Boring Panorama of a Man And a Mad Cow who m...
0.99
9
JUNGLE CLOSER
A Boring Character Study of a Boy And a Woman ...
0.99
10
LOATHING LEGALLY
A Boring Epistle of a Pioneer And a Mad Scient...
0.99
11
LOVERBOY ATTACKS
A Boring Story of a Car And a Butler who must ...
0.99
12
MARS ROMAN
A Boring Drama of a Car And a Dog who must Suc...
0.99
13
MOTHER OLEANDER
A Boring Tale of a Husband And a Boy who must ...
0.99
14
NONE SPIKING
A Boring Reflection of a Secret Agent And a As...
0.99
15
OLEANDER CLUE
A Boring Story of a Teacher And a Monkey who m...
0.99
16
POLISH BROOKLYN
A Boring Character Study of a Database Adminis...
0.99
17
SIEGE MADRE
A Boring Tale of a Frisbee And a Crocodile who...
0.99
18
STRAIGHT HOURS
A Boring Panorama of a Secret Agent And a Girl...
0.99
19
TIMBERLAND SKY
A Boring Display of a Man And a Dog who must R...
0.99
20
TREATMENT JEKYLL
A Boring Story of a Teacher And a Student who ...
0.99
In [17]:
is_099 = film_df.rental_rate == 0.99
is_boring = film_df.description.str.contains("Boring")
In [18]:
film_df[is_099 & is_boring].count()
Out[18]:
film_id 21
title 21
description 21
release_year 21
language_id 21
original_language_id 0
rental_duration 21
rental_rate 21
length 21
replacement_cost 21
rating 21
special_features 21
last_update 21
dtype: int64
In [19]:
film_df.rental_rate.unique()
Out[19]:
array([ 0.99, 4.99, 2.99])
In [20]:
SQL_QUERY = """
SELECT
DISTINCT rental_rate
FROM film
ORDER BY rental_rate
;
"""
pd.read_sql(SQL_QUERY, db)
Out[20]:
rental_rate
0
0.99
1
2.99
2
4.99
In [ ]:
In [22]:
SQL_QUERY = """
SELECT rating, COUNT(*) "total_films", AVG(rental_rate) "average_rental_rate"
FROM film
GROUP BY rating
ORDER BY average_rental_rate
;
"""
pd.read_sql(SQL_QUERY, db)
Out[22]:
rating
total_films
average_rental_rate
0
G
178
2.888876
1
R
195
2.938718
2
NC-17
210
2.970952
3
PG-13
223
3.034843
4
PG
194
3.051856
In [24]:
SQL_QUERY = """
SELECT
rating,
COUNT(*) "total_films",
AVG(rental_rate) "average_rental_rate"
FROM film
GROUP BY
1
ORDER BY 3
;
"""
pd.read_sql(SQL_QUERY, db)
Out[24]:
rating
total_films
average_rental_rate
0
G
178
2.888876
1
R
195
2.938718
2
NC-17
210
2.970952
3
PG-13
223
3.034843
4
PG
194
3.051856
In [ ]:
In [25]:
film_df.groupby("rating").agg({
"film_id": {"total films": np.size},
"rental_rate": {"average_rental_rate": np.mean},
})
Out[25]:
film_id
rental_rate
total films
average_rental_rate
rating
G
178
2.888876
NC-17
210
2.970952
PG
194
3.051856
PG-13
223
3.034843
R
195
2.938718
Content source: kimkipyo/dss_git_kkp
Similar notebooks: